"""
数据库增删改查
"""
from pymysql import Connection



conn = None



def connect():
    """
    创建数据库连接对象的方法
    :return: 数据库连接对象
    """
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        database='db2',
        charset='utf8',
        autocommit=True
    )
    return conn


def close():
    """
    关闭数据库的方法
    :param self:
    :return:
    """
    if conn:
        conn.close()


# 增加
def add(name, gender, age, address):
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 判断用户是否存在，不存在才能新增，存在不能新增
    user = findByName(name)
    print(user)
    if user and gender == user[2] and age == user[3] and address == user[4]:
        print('该用户已经存在')
    else:
        # 3. 定义sql
        sql = """
              insert into t_user values (null,%s, %s, %s, %s)
              """
        # 4. 执行SQL
        cursor.execute(sql, (name, gender, age, address))
        print('数据添加成功')
        # 5.关闭数据库
        conn.close()


# 删除 根据id删除
def deleteById(id):
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 根据id查询用户，看用户是否存在
    result = findById(id)
    if result:
        # 3. 定义sql
        sql = """
             delete from t_user where id = %s
              """
        # 4. 执行SQL
        cursor.execute(sql, (id))
        print('数据删除成功')
        # 5.关闭数据库
        conn.close()
    else:
        print('您要删除的用户不存在')



# 修改 根据id修改，修改一般将所有参数传递进来
def updateById(id,**kwargs):
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 得先确定要修改的数据是否存在
    result = findById(id)

    if result: # 数据存在可以修改
        sql = """
              update t_user \
              set u_name    = %s, \
                  gender  = %s, \
                  age     = %s, \
                  address = %s \
              where id = %s \
              """
        # 4. 执行SQL

        # 初始化变量（使用现有数据作为默认值）
        name = result[1]  # 假设result的结构是 (id, name, gender, age, address)
        gender = result[2]
        age = result[3]
        address = result[4]

        # 如果kwargs中有新值，则覆盖默认值
        if 'u_name' in kwargs:
            name = kwargs['u_name']
        if 'gender' in kwargs:
            gender = kwargs['gender']
        if 'age' in kwargs:
            age = kwargs['age']
        if 'address' in kwargs:
            address = kwargs['address']
        cursor.execute(sql, ( name,gender,age,address,id))
        print('数据修改成功')
        # 5.关闭数据库
        conn.close()
    else:
        print('您要修改的数据不存在')




# 查询
def findAll():
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 3. 定义sql
    sql = """
         select * from t_user
          """
    # 4. 执行SQL
    cursor.execute(sql)
    result = cursor.fetchall()
    for item in result:
        print(item)
    # 5.关闭数据库
    conn.close()

def findById(id):
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 3. 定义sql
    sql = """
         select * from t_user where id = %s
          """
    # 4. 执行SQL
    cursor.execute(sql, (id))
    result = cursor.fetchone()
    print(result)
    # 5.关闭数据库
    conn.close()
    return result

# 根据姓名查询
def findByName(uname):
    # 1. 连接数据库
    conn = connect()
    # 2. 获取游标对象
    cursor = conn.cursor()
    # 3. 定义sql
    sql = """
         select * from t_user where u_name = %s
          """
    # 4. 执行SQL
    cursor.execute(sql, (uname))
    result = cursor.fetchone()
    print(result)
    # 5.关闭数据库
    conn.close()
    return result

if __name__ == '__main__':
    # # add('姚大冲', '男', 19, '河北邯郸')
    # # deleteById(9)
    # # updateById(4, gender='女',address='北京海淀')
    # findAll()
    # # findById(4)
    # # findByName('姚晓冲')
   while True:
       print('输入1添加用户')
       print('输入2删除用户')
       num = int(input('请选择操作'))
       if num == 1:
           name = input('请输用户名：')
           gender = input('请输入姓名')
           age = int(input('请输入年龄'))
           address = input('请输入地址：')
           add(name,gender,age,address)

